<?php

namespace App\Model;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

class FinanceWithdrawOrder extends BaseModel
{
    protected $table = 'finance_withdraw_order';
    protected $userTable = 'user';
    protected $unionTable = 'union';
    protected $unionUserTable = 'union_user';
    protected $dailyRoomDataTable = 'daily_room_data';//每日统计表

    protected $primaryKey = 'order_id';
    public $timestamps = false;
    private $fields = ['order_id','order_sn','status','pay_timestamp','kk_amount','price','pay_type','out_trade_sn','create_timestamp','user_id','rule_id','merchant_no','fin_with_user_id', 'company_id'];
    protected $statCon = 'statistic';
    protected $newDailyRoomTable = 'new_daily_room';
    protected $statDailyTable = 'stat_daily';
    protected $stat_remain = 'stat_remain';
    public function getCreateTimestampAttribute($value){
        if(!$value) return 0;
        return date('Y-m-d H:i:s', $value);
    }

    public function getDismissTimestampAttribute($value){
        if(!$value) return 0;
        return date('Y-m-d H:i:s', $value);
    }

    public function getPayTimestampAttribute($value){
        if(!$value) return 0;
        return date('Y-m-d H:i:s', $value);
    }

    public function getTaxAfterAttribute($value){
        if(!$value) return 0;
        return $value / 100;
    }

    public function getPriceAttribute($value){
        if(!$value) return 0;
        return $value / 100;
    }

    public function getKkAmountAttribute($value){
        if(!$value) return 0;
        return $value / 100;
    }

    public function financeKkWithdrawRule(){
        return $this->belongsTo(FinanceKkWithdrawRule::class,'rule_id','rule_id');
    }

    public function numbers(){
        return $this->belongsTo(Number::class, 'user_id', 'user_id');
    }

    public function getList(Request $request){
        $page = $request->get('page', 0);
        $limit = $request->get('limit', 10);
        $uuid = $request->get('uuid', null);
        $nickname = $request->get('nickname', null);
        $status = $request->get('status',0);
        $begin_date = $request->get('begin_date',null);
        $end_date = $request->get('end_date', null);
        $op_type = $request->get('sort','create');
        $pay_type = $request->get('pay_type', 1);
        $accountType = $request->get('account_type', 0);
        $date_field = [ 'create' => 'create_timestamp', 'op' => 'pay_timestamp' ][$op_type];
        $out_trade_sn = $request->get('out_trade_sn', null);

        $query = FinanceWithdrawOrder::query();
        if($status >= 0) $query = $query->where('status', $status);
        if($begin_date && $end_date) $query = $query->whereRaw("FROM_UNIXTIME({$date_field},'%Y-%m-%d') BETWEEN '{$begin_date}' AND '{$end_date}'");
        if($begin_date && !$end_date) $query = $query->whereRaw("FROM_UNIXTIME({$date_field},'%Y-%m-%d') >= '{$begin_date}'");
        if(!$begin_date && $end_date) $query = $query->whereRaw("FROM_UNIXTIME({$date_field},'%Y-%m-%d') <= '{$end_date}'");
        if($pay_type >= 0) $query = $query->where('pay_type', $pay_type);
        $query = $query->where('account_type', $accountType);
        $query = $query->where('user_type', 0);
        if(!empty($out_trade_sn)) $query = $query->where('out_trade_sn', $out_trade_sn);
        $number = new Number();
        $number->getForbidList();
        $forbidUser = array_keys($number->forbidUser);
        $query = $query->whereHas('numbers',function($query) use($uuid, $nickname, $forbidUser){
            $query->select(['user_id']);
            if(!empty($forbidUser)) $query->whereNotIn('user_id',$forbidUser);
            if($uuid) $query->where('uuid', '=', $uuid);
            if($nickname) $query->where('nickname','like', "%$nickname%");
        });
        $query =  $query->with(['numbers:user_id,nickname,uuid,is_block','financeKkWithdrawRule:rule_id,kk_desc']);
        $data = $query->orderBy('order_id','DESC')->paginate($limit, $this->fields, 'page', $page);
        $data = objToArray($data);
        $kk_total = $query->sum('kk_amount');
        $data['kk_total'] = $kk_total;

        return $data;
    }

    public function getListV2(Request $request){
        $page = $request->get('page', 0);
        $limit = $request->get('limit', 10);
        $uuid = $request->get('uuid', null);
        $nickname = $request->get('nickname', null);
        $status = $request->get('status',0);
        $begin_date = $request->get('begin_date',null);
        $end_date = $request->get('end_date', null);
        $op_type = $request->get('sort','create');
        $date_field = [ 'create' => 'create_timestamp', 'op' => 'pay_timestamp' ][$op_type];
        $pay_type = $request->get('pay_type', 0);
        // $accountType = $request->get('account_type', 0);
        $out_trade_sn = $request->get('out_trade_sn', null);
        $union_id = $request->get('union_id', '');
        $union_name = $request->get('union_name', '');

        //被封用户不显示
        $number = new Number();
        $number->getForbidList();
        $notInUserIds = array_keys($number->forbidUser);
        //公会
        $unionIds = [];
        if(!empty($union_id) || !empty($union_name)) {
            $unionQuery = DB::connection('api')->table($this->unionTable)->select('id');
            if(!empty($union_id)) {
                $unionQuery = $unionQuery->where('id', $union_id);
            }
            if(!empty($union_name)) {
                $unionQuery = $unionQuery->where('name','like', "%$union_name%");
            }
            $unionIds = $unionQuery->get();
            $unionIds = objToArray($unionIds);
            if(empty($unionIds)) {
                return [];
            }
            $unionIds = array_column($unionIds, 'id');
        }
        //搜索项
        $query = DB::connection('api')->table($this->table.' as a');
        $query = $query->leftJoin($this->userTable.' as b', "a.user_id", "=", "b.user_id");
        $query = $query->leftJoin($this->unionUserTable.' as c', "a.user_id", "=", "c.user_id");
        // $query = $query->where('a.account_type', $accountType);
        $query = $query->where('a.user_type', 0);
        if($status >= 0) {
            $query = $query->where('a.status', $status);
        }
        if(!empty($uuid)) {
            $query = $query->where('b.uuid', $uuid);
        }
        if(!empty($nickname)) {
            $query = $query->where('b.nickname','like', "%$nickname%");
        }
        if($pay_type > 0) {
            $query = $query->where('a.pay_type', $pay_type);
        }
        if(!empty($out_trade_sn)) {
            $query = $query->where('a.out_trade_sn', $out_trade_sn);
        }
        if(!empty($notInUserIds)) {
            $query = $query->whereNotIn('a.user_id', $notInUserIds);
        }
        if(!empty($unionIds)) {
            $query = $query->whereIn('c.un_id', $unionIds);
        }
        if(!empty($begin_date)) {
            $begin_date = strtotime($begin_date.' 00:00:00');
            $query = $query->where('a.'.$date_field, '>=', $begin_date);
        }
        if(!empty($end_date)) {
            $end_date = strtotime($end_date.' 23:59:59');
            $query = $query->where('a.'.$date_field, '<=', $end_date);
        }
        $fields = ['a.order_id','a.order_sn','a.status','a.pay_timestamp','a.kk_amount','a.price','a.pay_type','a.out_trade_sn','a.create_timestamp','a.user_id','a.remark'
                ,'b.nickname','b.uuid','b.is_block'
                ,'c.un_id as union_id'
        ];
        $data = $query->orderBy('a.order_id','DESC')->paginate($limit, $fields, 'page', $page);
        $data = objToArray($data);
        if(!empty($data['data'])) {
            $unionIds = array_column($data['data'], 'union_id');
            $unionList = DB::connection('api')->table($this->unionTable)->whereIn('id', $unionIds)->select(['id','name'])->get();
            $unionList = array_column(objToArray($unionList), null, 'id');

            foreach($data['data'] as $key=>$val) {
                $array = explode("\n", $val['remark']);
                $remark = array_map(function ($array) {
                    // 使用列表解析（list()）将每个键值对分割为键和值
                    if (trim($array) !== '' && strpos($array, ':') !== false) {
                        list($key, $value) = explode(":", $array);
                    } else {
                        $value = '';
                    }
                    return $value; // 返回值
                }, $array);


                $val['numbers'] = [
                    'user_id' => $val['user_id'],
                    'nickname' => $val['nickname'],
                    'uuid' => $val['uuid'],
                    'is_block' => $val['is_block'],
                ];
                $val['union_name'] = empty($unionList[$val['union_id']]['name']) ? '' : $unionList[$val['union_id']]['name'];
                $val['create_timestamp'] = empty($val['create_timestamp']) ? '' : date("Y-m-d H:i:s", $val['create_timestamp']);
                $val['pay_timestamp'] = empty($val['pay_timestamp']) ? '' : date("Y-m-d H:i:s", $val['pay_timestamp']);
                $val['price'] = (string) round($val['price']/100, 2);
                $val['payment_account'] = $remark['0'];
                $val['payment_name'] = $remark['1'];
                $val['payment_rank'] = $remark['2'];
                $val['payment_rank_hu'] = $remark['3'];
                $data['data'][$key] = $val;
            }
        }

        $kk_total = $query->sum('kk_amount');
        $data['kk_total'] = $kk_total;

        return $data;
    }

    /**
     * 收支统计
     * @param $starttime
     * @param $endtime
     * @param $dm
     * @return mixed
     */
    public function accountStatistics($input = []){
        $dm = $input['type'] ?: 'd';
        $starttime = $input['start_date'] ?? '';
        $endtime = $input['end_date'] ?? '';
        $page = $input['page'];
        $limit = $input['limit'];
        $page = ($page-1) * $limit;

        $now = time();
        $timeBase = '2023-06-01';
        //项目起始日期
        if(empty($starttime) || (!empty($starttime) && strtotime($starttime)<strtotime($timeBase))) {
            $starttime = $timeBase;
        }
        if(empty($endtime) || (!empty($endtime) && strtotime($endtime)>$now)) {
            $endtime = date("Y-m-d", $now);
        }
        $starttime = strtotime($starttime);
        $endtime = strtotime($endtime);

        $list = [];
        //按日统计
        if($dm == 'd') {
            while ($starttime <= $endtime) {
                $list[] = date('Y-m-d', $starttime);
                $starttime = $starttime + 86400;
            }
            $format = '%Y-%m-%d';
        }

        //按月统计
        if($dm == 'm') {
            $starttime = strtotime(date('Y-m-01', $starttime));
            while ($starttime <= $endtime) {
                $list[] = date('Y-m', $starttime);
                $starttime = strtotime('+1 month', $starttime);
            }
            $format = '%Y-%m';
        }
        $list = array_reverse($list);

        $total = count($list);
        $data = [];
        do {
            //分页
            $list = array_slice($list, $page, $limit);
            if(empty($list)) {
                break;
            }
            $timeSt = end($list);
            $timeEd = $list[0];

            if($dm == 'm') {
                $timeSt = $timeSt.'-01';
                $timeEd = date('Y-m-t', strtotime($timeEd.'-01'));
            }

            //充值
            $res1 = DB::connection('api')->table('finance_recharge_order')
                ->where('status',1)
                ->where('pay_timestamp', '>=', strtotime($timeSt.' 00:00:00'))
                ->where('pay_timestamp', '<=', strtotime($timeEd.' 23:59:59'))
                ->selectRaw("FROM_UNIXTIME(pay_timestamp,'{$format}' ) as pay_time, SUM(price) as pay_in")
                ->groupBy('pay_time')
                ->get();
            $res1 = array_column(objToArray($res1), null, 'pay_time');

            //个人提现
            $res2 = DB::connection('api')->table('finance_withdraw_order')
                ->where('status',1)
                ->where('pay_timestamp', '>=', strtotime($timeSt.' 00:00:00'))
                ->where('pay_timestamp', '<=', strtotime($timeEd.' 23:59:59'))
                ->selectRaw("FROM_UNIXTIME(pay_timestamp,'{$format}' ) as pay_time, SUM(price) as pay_out, SUM(tax_after) as tax")
                ->groupBy('pay_time')
                ->get();

            $res2 = array_column(objToArray($res2), null, 'pay_time');

            //对公结算
            $res3 = DB::connection('api')->table('union_settle')
                ->where('created_at', '>=', $timeSt.' 00:00:00')
                ->where('created_at', '<=', $timeEd.' 23:59:59')
                ->selectRaw("FROM_UNIXTIME(UNIX_TIMESTAMP(created_at),'{$format}' ) as pay_time, SUM(settle_kk) as settle_out")
                ->groupBy('pay_time')
                ->get();
            $res3 = array_column(objToArray($res3), null, 'pay_time');

            //消费流水
//            $res4 = DB::table($this->dailyRoomDataTable)
//                ->where('created_at', '>=', $timeSt.' 00:00:00')
//                ->where('created_at', '<=', $timeEd.' 23:59:59')
//                ->selectRaw("FROM_UNIXTIME(UNIX_TIMESTAMP(created_at),'{$format}') as pay_time, SUM(totalDiamond) as consumption_flow")
//                ->groupBy('pay_time')
//                ->get();
            //消费流水
            $res4 = DB::connection($this->statCon)->table($this->newDailyRoomTable)
                ->where('created_date', '>=', $timeSt.' 00:00:00')
                ->where('created_date', '<=', $timeEd.' 23:59:59')
//                ->selectRaw("FROM_UNIXTIME(UNIX_TIMESTAMP(created_date),'{$format}') as pay_time, SUM(useDiamondAll) as consumption_flow,SUM(useDiamondAll)/SUM(rechargeAll) as recharge_ratio")
                ->selectRaw("FROM_UNIXTIME(UNIX_TIMESTAMP(created_date),'{$format}') as pay_time, SUM(useDiamondAll) as consumption_flow")
                ->groupBy('pay_time')
                ->get();

            $res4 = array_column(objToArray($res4), null, 'pay_time');
//            $res5 = DB::connection($this->statCon)->table($this->statDailyTable)
//                ->where('created_date', '>=', $timeSt.' 00:00:00')
//                ->where('created_date', '<=', $timeEd.' 23:59:59')
//                ->selectRaw("FROM_UNIXTIME(UNIX_TIMESTAMP(created_date),'{$format}') as pay_time, SUM(useDiamondAll) as consumption_flow,SUM(rechargeAll)/SUM(useDiamondAll) as recharge_ratio")
//                ->groupBy('pay_time')
//                ->get();
//
//            $res5 = array_column(objToArray($res5), null, 'pay_time');
            $res5 = DB::connection($this->statCon)->table($this->stat_remain)
                ->where('created_date', '>=', $timeSt.' 00:00:00')
                ->where('created_date', '<=', $timeEd.' 23:59:59')
                ->selectRaw("FROM_UNIXTIME(UNIX_TIMESTAMP(created_date),'{$format}') as pay_time,useDiamondAll,useKkBalanceAll,useBagGiftAll")
//                ->groupBy('pay_time')
                ->get();
            $res5 = array_column(objToArray($res5), null, 'pay_time');


            $wallet = DB::connection('api')->table('finance_wallet')
                ->selectRaw('sum(diamond_balance) as diamond_count,sum(kk_balance) as kk_balance_count')
                ->where('diamond_balance', '>', 0)
                ->orWhere('kk_balance', '>', 0)
                ->first();
            $wallet = self::objToArray($wallet);
            $gift_bag = DB::connection('api')->table('gift_bag as a')
                ->leftJoin('gift_library as b' ,'a.gift_id','=','b.gift_id')
                ->where('a.avail', '>', 0)
                ->selectRaw('sum(avail*price) as bag_diamond_num')
                ->first();
            $gift_bag = self::objToArray($gift_bag);

            $res5 = [
                'useDiamondAll' => $wallet['diamond_count'],
                'useKkBalanceAll' => $wallet['kk_balance_count'],
                'useBagGiftAll' => $gift_bag['bag_diamond_num'],
            ];


            foreach($list as $k => $val) {
//                $recharge_ratio=$res1[$val]['pay_in']/$res4[$val]['consumption_flow'];
                $item = [];
                if ($k == 0) {
                    $item['settle_out'] = "钻石:".$res5['useDiamondAll']."---金币:".$res5['useKkBalanceAll']."---礼物:".$res5['useBagGiftAll'];
                } else {
                    $item['settle_out'] = '';
                }

                $item['pay_time'] = $val;
                $item['pay_in'] = empty($res1[$val]['pay_in']) ? 0 : round($res1[$val]['pay_in']/100, 2);
                $item['pay_out'] = empty($res2[$val]['pay_out']) ? 0 : round($res2[$val]['pay_out']/100, 2);
                $item['tax'] = empty($res2[$val]['tax']) ? 0 : round($res2[$val]['tax'], 2);
                //$item['settle_out'] = empty($res3[$val]['settle_out']) ? 0 : round($res3[$val]['settle_out']/10, 1);
//                $item['settle_out'] = empty($res5[$val]['useDiamondAll']) ? 0 : "钻石:".$res5[$val]['useDiamondAll']."---金币:".$res5[$val]['useKkBalanceAll']."---礼物:".$res5[$val]['useBagGiftAll'];
                $item['consumption_flow'] = empty($res4[$val]['consumption_flow']) ? 0 : round($res4[$val]['consumption_flow']/10, 2); //充值流水
//                $item['recharge_ratio'] = empty($res4[$val]['recharge_ratio']) ? 0 : round($res4[$val]['recharge_ratio']*10, 2); //充值比例;
                $item['recharge_ratio'] = $item['pay_in']>0 && $item['consumption_flow'] ?round(round($item['consumption_flow'],2)/round($item['pay_in'],2),4) : 0; //充值比例

                $data[] = $item;
            }

        } while (false);

        return ['data' => $data, 'total' => $total];
    }

    public function statisticsWithdrawAuditAll($forbid_user, int $accountType){
        $sql = "
SELECT
  SUM(price) / 100 total_price,
  SUM(CASE WHEN user_id IN ('{$forbid_user}') THEN price ELSE 0 END) / 100 forbid_price
FROM
  zb_finance_withdraw_order
WHERE
  status=0 and user_type = 0 and account_type = {$accountType}";
        return DB::connection('api')->selectOne($sql);
    }

}
